package com.zzw.spring.jdbctemplate;

import com.zzw.spring.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;
import java.sql.*;
import java.util.List;

//@Component
public class JdbcTemplateTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private DataSourceTransactionManager dataSource;

    public static void createTable(){
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost:3306/sample");
        ds.setUsername("root");
        ds.setPassword("123456");

        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(ds);
        String sql = "create table t_user(user_id int primary key,user_name varchar(60))";
        jdbcTemplate.execute(sql);
    }

    public void insertUser(){
        try {
            Connection connection = dataSource.getDataSource().getConnection();
            Statement statement = connection.createStatement();
            String sql = "INSER INTO t_user(user_id,user_name) VALUES('hello')";
            statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
            ResultSet rs = statement.getGeneratedKeys();
            if(rs.next()){
                int key = rs.getInt(0);
                System.out.println(key);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void update(){
        final String sql = "INSER INTO t_user(user_id,user_name) VALUES(?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setString(1,"hello");
                return null;
            }
        },keyHolder);
        System.out.println(keyHolder.getKey().intValue());
    }

    //批量添加
    public void updates(List<User> users){
        final String sql = "INSERT INTO t_user(user_name) VALUES(?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                User user = users.get(i);
                ps.setString(1,user.getUser_name());
            }

            @Override
            public int getBatchSize() {
                return users.size();
            }
        });
    }

    //查询
    public User getUser(final int userId){
        String sql = "SELECT user_id,user_name FROM t_user where user_id = 1";
        final User user = new User();
        jdbcTemplate.query(sql, new Object[]{userId}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                user.setUser_id(userId);
                user.setUser_name(resultSet.getString("user_name"));
            }
        });
        return user;
    }

    //通过RowMapper查询
    public List<User> getUserByRowMapper(final int userId){
        User user = new User();
        String sql = "SELECT user_id,user_name FROM t_user where user_id = 1";
        return jdbcTemplate.query(sql, new RowMapper<User>() {

            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                user.setUser_id(userId);
                user.setUser_name(resultSet.getString("user_name"));
                return user;
            }
        });
    }

    //获取数量
    public int count(){
        String sql = "select count(user_id) from t_user";
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    public static void main(String[] args) {
        createTable();
    }
}
